﻿'************************************************************************************'
'* Copyright (c) 2009, Patrick Driscoll All rights reserved.                        *'
'*                                                                                  *'
'* Redistribution and use in source and binary forms, with or without               *'
'* modification, are permitted provided that the following conditions are met:      *'
'*                                                                                  *'
'* Redistributions of source code must retain the above copyright notice, this      *'
'* list of conditions and the following disclaimer. Redistributions in binary       *'
'* form must reproduce the above copyright notice, this list of conditions and      *'
'* the following disclaimer in the documentation and/or other materials             *'
'* provided with the distribution. Neither the name of the DriscollArt Software nor *'
'* the names of its contributors may be used to endorse or promote products         *'
'* derived from this software without specific prior written permission.            *'
'*                                                                                  *'
'* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"      *'
'* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE        *'
'* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE       *'
'* ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE FOR      *'
'* ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL           *'
'* DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR       *'
'* SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER       *'
'* CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT               *'
'* LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY        *'
'* OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH      *'
'* DAMAGE.                                                                          *'
'************************************************************************************'

Imports Microsoft.VisualBasic
Imports System
Imports System.Text
Imports System.CodeDom.Compiler
Imports System.Reflection
Imports System.IO
Imports System.Diagnostics
Imports System.Web

''' <summary>
''' .NET code execution class
''' </summary>
''' <remarks>
''' This class is based on a codeproject.com submission from this author:
''' http://www.codeproject.com/Members/wduros1
''' from the following project:
''' http://www.codeproject.com/KB/dotnet/VBRunNET.aspx
''' Thanks wduros1!
''' </remarks>
Public Class cVBEvalProvider
	Inherits EvalProvider

    Public Sub New()
        MyBase.New()
	End Sub

    Public Function Eval(ByVal vbCode As String) As Object

        Dim oCodeProvider As VBCodeProvider = New VBCodeProvider
        ' Obsolete in 2.0 framework
        ' Dim oICCompiler As ICodeCompiler = oCodeProvider.CreateCompiler

        Dim oCParams As CompilerParameters = New CompilerParameters
        Dim oCResults As CompilerResults
        Dim oAssy As System.Reflection.Assembly
        Dim oExecInstance As Object = Nothing
        Dim oRetObj As Object = Nothing
        Dim oMethodInfo As MethodInfo
        Dim oType As Type


        Try

            ' Setup the Compiler Parameters  
            ' Add any referenced assemblies
            oCParams.ReferencedAssemblies.Add("system.dll")
            oCParams.ReferencedAssemblies.Add("system.xml.dll")
            oCParams.ReferencedAssemblies.Add("system.data.dll")
            oCParams.CompilerOptions = "/t:library"
            oCParams.GenerateInMemory = True

            ' Generate the Code Framework
            Dim sb As StringBuilder = New StringBuilder("")

            sb.Append("Imports System" & vbCrLf)
            sb.Append("Imports System.Xml" & vbCrLf)
            sb.Append("Imports System.Data" & vbCrLf)

            ' Build a little wrapper code, with our passed in code in the middle 
            sb.Append("Namespace dValuate" & vbCrLf)
            sb.Append("Class EvalRunTime " & vbCrLf)
            sb.Append("Public Function EvaluateIt() As Object " & vbCrLf)
            sb.Append(vbCode & vbCrLf)
            sb.Append("End Function " & vbCrLf)
            sb.Append("End Class " & vbCrLf)
            sb.Append("End Namespace" & vbCrLf)
            Debug.WriteLine(sb.ToString())

            Try
                ' Compile and get results 
                ' 2.0 Framework - Method called from Code Provider
                oCResults = oCodeProvider.CompileAssemblyFromSource(oCParams, sb.ToString)
                ' 1.1 Framework - Method called from CodeCompiler Interface
                ' cr = oICCompiler.CompileAssemblyFromSource (cp, sb.ToString)


                ' Check for compile time errors 
                If oCResults.Errors.Count <> 0 Then

                    Me.CompilerErrors = oCResults.Errors
                    Throw New Exception("Compile Errors")

                Else
                    ' No Errors On Compile, so continue to process...

                    oAssy = oCResults.CompiledAssembly
                    oExecInstance = oAssy.CreateInstance("dValuate.EvalRunTime")


                    oType = oExecInstance.GetType
                    oMethodInfo = oType.GetMethod("EvaluateIt")

                    oRetObj = oMethodInfo.Invoke(oExecInstance, Nothing)
                    Return oRetObj

                End If

            Catch ex As Exception
                ' Compile Time Errors Are Caught Here
                ' Some other weird error 
                Debug.WriteLine(ex.Message)
                Stop
            End Try

        Catch ex As Exception
            Debug.WriteLine(ex.Message)
            Stop
        End Try

        Return oRetObj

    End Function

	Public Overrides Function GenerateDNNCode(ByVal vbCode As String, ByVal DNNProperties As Collection, ByVal DatabaseProperties As DataTable) As String
		Dim oCodeProvider As VBCodeProvider = New VBCodeProvider
		' Obsolete in 2.0 framework
		' Dim oICCompiler As ICodeCompiler = oCodeProvider.CreateCompiler

		Dim oCParams As CompilerParameters = New CompilerParameters
		Dim oCResults As CompilerResults
		Dim oAssy As System.Reflection.Assembly
		Dim oExecInstance As Object = Nothing
		Dim oRetObj As Object = Nothing
		Dim oMethodInfo As MethodInfo
		Dim oType As Type

		Try

			' Setup the Compiler Parameters  
			' Add any referenced assemblies
			oCParams.ReferencedAssemblies.Add("system.dll")
			oCParams.ReferencedAssemblies.Add("system.xml.dll")
			oCParams.ReferencedAssemblies.Add("system.data.dll")
			oCParams.ReferencedAssemblies.Add("system.web.dll")
			oCParams.ReferencedAssemblies.Add("microsoft.visualbasic.dll")
			oCParams.CompilerOptions = "/t:library"
			oCParams.GenerateInMemory = True

			' Generate the Code Framework
			Dim sb As StringBuilder = New StringBuilder("")

			sb.Append("Imports System" & vbCrLf)
			sb.Append("Imports System.Xml" & vbCrLf)
			sb.Append("Imports System.Data" & vbCrLf)
			sb.Append("Imports Microsoft.VisualBasic" & vbCrLf)
			sb.Append("Imports System.Text" & vbCrLf)
			sb.Append("Imports System.Web" & vbCrLf)

			' Build a little wrapper code, with our passed in code in the middle 
			sb.Append("Namespace dValuate" & vbCrLf)
			sb.Append("Class EvalRunTime " & vbCrLf)
			'sb.Append("Public Function GenerateCodeString(Optional ByVal DNNProperties As Collection = Nothing, Optional ByVal DatabaseProperties As Collection = Nothing) As Object" & vbCrLf)
			sb.Append(vbCode & vbCrLf)
			'sb.Append("End Function " & vbCrLf)
			sb.Append("End Class " & vbCrLf)
			sb.Append("End Namespace" & vbCrLf)
			Debug.WriteLine(sb.ToString())

			Try
				' Compile and get results 
				' 2.0 Framework - Method called from Code Provider
				oCResults = oCodeProvider.CompileAssemblyFromSource(oCParams, sb.ToString)
				' 1.1 Framework - Method called from CodeCompiler Interface
				' cr = oICCompiler.CompileAssemblyFromSource (cp, sb.ToString)


				' Check for compile time errors 
				If oCResults.Errors.Count <> 0 Then

					Me.CompilerErrors = oCResults.Errors
					For Each comperr As CompilerError In oCResults.Errors
						Debug.WriteLine("Line " & comperr.Line & ": " & comperr.ErrorNumber & " - " & comperr.ErrorText)
					Next
					Throw New Exception("Compile Errors")

				Else
					' No Errors On Compile, so continue to process...

					oAssy = oCResults.CompiledAssembly
					oExecInstance = oAssy.CreateInstance("dValuate.EvalRunTime")

					oType = oExecInstance.GetType
					oMethodInfo = oType.GetMethod("GenerateCodeString")

					Dim arrParameters(1) As Object

					arrParameters(0) = DNNProperties
					arrParameters(1) = DatabaseProperties

					oRetObj = oMethodInfo.Invoke(oExecInstance, arrParameters)
					Return oRetObj

				End If

			Catch ex As Exception
				' Compile Time Errors Are Caught Here
				' Some other weird error 
				Debug.WriteLine(ex.Message)
				Stop
			End Try

		Catch ex As Exception
			Debug.WriteLine(ex.Message)
			Stop
		End Try

		Return oRetObj

	End Function

	Public Overrides Sub SaveDNNCode(ByVal vbCode As String, ByVal DNNProperties As Collection, ByVal DatabaseProperties As DataTable)
		Dim oCodeProvider As VBCodeProvider = New VBCodeProvider
		' Obsolete in 2.0 framework
		' Dim oICCompiler As ICodeCompiler = oCodeProvider.CreateCompiler

		Dim oCParams As CompilerParameters = New CompilerParameters
		Dim oCResults As CompilerResults
		Dim oAssy As System.Reflection.Assembly
		Dim oExecInstance As Object = Nothing
		Dim oRetObj As Object = Nothing
		Dim oMethodInfo As MethodInfo
		Dim oType As Type

		Try

			' Setup the Compiler Parameters  
			' Add any referenced assemblies
			oCParams.ReferencedAssemblies.Add("system.dll")
			oCParams.ReferencedAssemblies.Add("system.xml.dll")
			oCParams.ReferencedAssemblies.Add("system.data.dll")
			oCParams.ReferencedAssemblies.Add("system.web.dll")
			oCParams.ReferencedAssemblies.Add("microsoft.visualbasic.dll")
			oCParams.CompilerOptions = "/t:library"
			oCParams.GenerateInMemory = True

			' Generate the Code Framework
			Dim codeString = MyBase.evalClassBegin & vbCrLf & vbCode & vbCrLf & MyBase.evalClassEnd
			Debug.WriteLine(codeString)

			Try
				' Compile and get results 
				' 2.0 Framework - Method called from Code Provider
				oCResults = oCodeProvider.CompileAssemblyFromSource(oCParams, codeString)
				' 1.1 Framework - Method called from CodeCompiler Interface
				' cr = oICCompiler.CompileAssemblyFromSource (cp, sb.ToString)


				' Check for compile time errors 
				If oCResults.Errors.Count <> 0 Then

					Me.CompilerErrors = oCResults.Errors
					For Each comperr As CompilerError In oCResults.Errors
						Debug.WriteLine("Line " & comperr.Line & ": " & comperr.ErrorNumber & " - " & comperr.ErrorText)
					Next
					Throw New Exception("Compile Errors")

				Else
					' No Errors On Compile, so continue to process...

					oAssy = oCResults.CompiledAssembly
					oExecInstance = oAssy.CreateInstance("dValuate.EvalRunTime")

					oType = oExecInstance.GetType

					oMethodInfo = oType.GetMethod("SaveCodeFile")

					Dim arrParameters(1) As Object

					arrParameters(0) = DNNProperties
					arrParameters(1) = DatabaseProperties

					oMethodInfo.Invoke(oExecInstance, arrParameters)

				End If

			Catch ex As Exception
				' Compile Time Errors Are Caught Here
				' Some other weird error 
				Debug.WriteLine(ex.Message)
				Stop
			End Try

		Catch ex As Exception
			Debug.WriteLine(ex.Message)
			Stop
		End Try



	End Sub

    Public Sub SaveCodeFile(Optional ByVal DNNProperties As Collection = Nothing, Optional ByVal DatabaseProperties As DataTable = Nothing)
        Dim objCodePath As New System.IO.DirectoryInfo(DNNProperties.Item("RootDNNPath").ToString & DNNProperties.Item("Company").ToString & "." & DNNProperties.Item("ClassName").ToString & "/App_LocalResources")
        If objCodePath.Exists = False Then
            objCodePath.Create()
        End If
        Dim strFileName As String = objCodePath.FullName & "\Settings.ascx"

        Dim objStringWriter As New System.IO.StreamWriter(New System.IO.FileStream(strFileName, FileMode.Create, FileAccess.Write))

        objStringWriter.Write(GenerateCodeString(DNNProperties, DatabaseProperties))
        objStringWriter.Close()

    End Sub

    Public Function GenerateCodeString(Optional ByVal DNNProperties As Collection = Nothing, Optional ByVal DatabaseProperties As DataTable = Nothing) As Object
        Dim StringBuilder_Settings As New StringBuilder
        StringBuilder_Settings.AppendLine("<%@ Control Language=""vb"" AutoEventWireup=""false"" CodeFile=""Settings.ascx.vb"" Inherits=""" & DNNProperties.Item("Company") & ".Modules." & DNNProperties.Item("ClassName") & ".Settings"" %>")
        StringBuilder_Settings.AppendLine("<%@ Register TagPrefix=""dnn"" TagName=""Label"" Src=""~/controls/LabelControl.ascx"" %>")
        StringBuilder_Settings.AppendLine("<table cellspacing=""0"" cellpadding=""2"" border=""0"" summary=""DATaskSheet Settings Design Table"">")
        StringBuilder_Settings.AppendLine("  <tr>")
        StringBuilder_Settings.AppendLine("   <td class=""SubHead"" width=""150""><dnn:label id=""lblTemplate"" runat=""server"" controlname=""txtTemplate"" suffix="":""></dnn:label></td>")
        StringBuilder_Settings.AppendLine("   <td valign=""bottom"" >")
        StringBuilder_Settings.AppendLine("    <asp:textbox id=""txtTemplate"" cssclass=""NormalTextBox"" width=""390"" columns=""30"" textmode=""MultiLine"" rows=""10"" maxlength=""2000"" runat=""server"" />")
        StringBuilder_Settings.AppendLine("   </td>")
        StringBuilder_Settings.AppendLine("  </tr>")
        StringBuilder_Settings.AppendLine("</table>")
        Return StringBuilder_Settings.ToString
    End Function

    Private Function GetPropertyAssignments(ByVal DatabaseProperties As DataTable) As String
        Dim StringBuilder_Properties As New StringBuilder

        For Each objRow As DataRow In DatabaseProperties.Rows
            If objRow.Item("fieldname") <> "" Then
                If objRow.Item("controltype") = "" Then
                    If objRow.Item("PK") Then
                        StringBuilder_Properties.AppendLine("                 ." & objRow.Item("fieldname") & " = _" & objRow.Item("fieldname"))
                    Else
                        StringBuilder_Properties.AppendLine("                 ' TODO: assign value for " & objRow.Item("fieldname"))
                        StringBuilder_Properties.AppendLine("                 '." & objRow.Item("fieldname") & " = [SOME VALUE]")
                    End If
                Else
                    If objRow.Item("controltype") = "CheckBox" Then
                        StringBuilder_Properties.AppendLine("                 ." & objRow.Item("fieldname") & " = " & objRow.Item("controltype") & "_" & objRow.Item("fieldname") & ".Checked")
                    Else
                        StringBuilder_Properties.AppendLine("                 ." & objRow.Item("fieldname") & " = " & objRow.Item("controltype") & "_" & objRow.Item("fieldname") & ".Text")
                    End If
                End If
            End If
        Next

        Return StringBuilder_Properties.ToString
    End Function

    Private Function GenerateCommentBlock(ByVal ModuleName As String, ByVal ControlName As String, ByVal Comments As String) As String
        Dim StringBuilder_CommentBlock As New System.Text.StringBuilder
        StringBuilder_CommentBlock.AppendLine("    ''' -----------------------------------------------------------------------------")
        StringBuilder_CommentBlock.AppendLine("    ''' <summary>")
        StringBuilder_CommentBlock.AppendLine("    ''' " & ModuleName & " (" & ControlName & ") ''' ")
        StringBuilder_CommentBlock.AppendLine("    ''' " & Comments & "    ''' ")
        StringBuilder_CommentBlock.AppendLine("    ''' </summary>")
        StringBuilder_CommentBlock.AppendLine("    ''' <remarks>")
        StringBuilder_CommentBlock.AppendLine("    ''' </remarks>")
        StringBuilder_CommentBlock.AppendLine("    ''' <history>")
        StringBuilder_CommentBlock.AppendLine("    ''' </history>")
        StringBuilder_CommentBlock.AppendLine("    ''' -----------------------------------------------------------------------------")
        Return StringBuilder_CommentBlock.ToString
    End Function

    Private Function GetFieldList(ByVal Operation As String, ByVal DBProperties As DataTable, Optional ByVal ShowDataType As Boolean = False) As String
        Dim strFields As String = ""
        For Each objRow As DataRow In DBProperties.Rows
            If objRow.Item("fieldname") <> "" Then
                If Operation = "" OrElse objRow.Item("C") AndAlso Operation = "C" OrElse objRow.Item("R") AndAlso Operation = "R" OrElse objRow.Item("U") AndAlso Operation = "U" OrElse objRow.Item("D") AndAlso Operation = "D" OrElse objRow.Item("L") AndAlso Operation = "L" Then
                    If strFields = "" Then
                        strFields &= objRow.Item("fieldname")
                    Else
                        strFields &= "," & objRow.Item("fieldname")
                    End If

                    If ShowDataType Then
                        strFields &= " " & objRow.Item("sqldatatype") & GetFieldLength(objRow.Item("length")) & " " ' vbCrLf
                    Else
                        strFields &= " " 'vbCrLf
                    End If
                End If
            End If
        Next

        Return strFields
    End Function

    Private Function GetPrimaryKey(ByVal DatabaseProperies As DataTable) As String
        Dim strPK As String = "[PLEASE DEFINE A PRIMARY KEY]"
        For Each objRow As DataRow In DatabaseProperies.Rows()
            If objRow.Item("fieldname") <> "" AndAlso objRow.Item("PK") Then
                strPK = objRow.Item("fieldname")
                Exit For
            End If
        Next
        Return strPK
    End Function

    Private Function GenerateSQLComments(ByVal ProcedureName As String, ByVal TableName As String, ByVal Company As String, ByVal Author As String, ByVal Operation As String) As String
        Dim StringBuilder_CommentHeader As New StringBuilder
        Dim strDescription As String

        Select Case Operation
            Case "C"
                strDescription = "Create one record in the " & TableName & " table "
            Case "R"
                strDescription = "Retrieve one record in the " & TableName & " table "
            Case "U"
                strDescription = "Update one record in the " & TableName & " table "
            Case "D"
                strDescription = "Delete one record in the " & TableName & " table "
            Case "L"
                strDescription = "List records from the " & TableName & " table "
            Case Else
                strDescription = "INVALID OPERATION"
        End Select
        StringBuilder_CommentHeader.AppendLine("-- =============================================")
        StringBuilder_CommentHeader.AppendLine("-- Author: " & Author & "   Company: " & Company)
        StringBuilder_CommentHeader.AppendLine("-- Procedure Name: " & ProcedureName)
        StringBuilder_CommentHeader.AppendLine("-- Date Created: " & Now.ToString("MM/dd/yyyy"))
        StringBuilder_CommentHeader.AppendLine("-- Description:	" & strDescription)
        StringBuilder_CommentHeader.AppendLine("-- =============================================")

        Return StringBuilder_CommentHeader.ToString
    End Function

    Private Function GetTableFields(ByVal DatabaseProperties As DataTable) As String
        Dim StringBuilder_TableFields As New StringBuilder
        Dim objRow As DataRow
        Dim strIdentity As String

        For Each objRow In DatabaseProperties.Rows
            If objRow.Item("fieldname") <> String.Empty Then
                If objRow.Item("PK") AndAlso objRow.Item("sqldatatype") = "int" Then
                    strIdentity = " IDENTITY (1, 1)"
                Else
                    strIdentity = ""
                End If

                If StringBuilder_TableFields.ToString <> "" Then
                    StringBuilder_TableFields.Append(",")
                End If
                StringBuilder_TableFields.Append("[" & objRow.Item("fieldname") & "] [" & objRow.Item("sqldatatype") & GetFieldLength(objRow.Item("length")) & "] " & strIdentity & " NOT NULL" & vbCrLf)
            End If
        Next

        Return StringBuilder_TableFields.ToString
    End Function

    Private Function GetFieldLength(ByVal strLength As String) As String
        If IsNumeric(strLength) Then
            Return "(" & strLength & ")"
        Else
            Return ""
        End If
    End Function

    Private Function GetPrimaryKeys(ByVal DNNProperties As Collection, ByVal DatabaseProperties As DataTable) As String
        Dim StringBuilder_PrimaryKeys As New StringBuilder
        For Each objRow As DataRow In DatabaseProperties.Rows
            If objRow.Item("fieldname") <> "" AndAlso objRow.Item("PK") Then
                StringBuilder_PrimaryKeys.AppendLine("ALTER TABLE {databaseOwner}[{objectQualifier}" & DNNProperties.Item("SqlTableName") & "] ADD CONSTRAINT [PK_{objectQualifier}" & DNNProperties.Item("SqlTableName") & "_" & objRow.Item("fieldname") & "] PRIMARY KEY CLUSTERED  ([" & objRow.Item("fieldname") & "])")
            End If
        Next

        Return StringBuilder_PrimaryKeys.ToString
    End Function

    Private Function GetKeyList(ByVal Operation As String, ByVal DBProperties As DataTable) As String
        Dim strKeys As String = ""
        For Each objRow As DataRow In DBProperties.Rows
            If Operation = "" OrElse objRow.Item("C") AndAlso Operation = "C" OrElse objRow.Item("R") AndAlso Operation = "R" OrElse objRow.Item("U") AndAlso Operation = "U" OrElse objRow.Item("D") AndAlso Operation = "D" OrElse objRow.Item("L") AndAlso Operation = "L" Then
                If strKeys = "" Then
                    strKeys &= "ByVal " & objRow.Item("fieldname") & " As " & GetVBDataType(objRow.Item("sqldatatype"))
                Else
                    strKeys &= ", ByVal " & objRow.Item("fieldname") & " As " & GetVBDataType(objRow.Item("sqldatatype"))
                End If
            End If
        Next

        Return strKeys
    End Function

    Private Function GetParmList(ByVal Operation As String, ByVal DBProperties As DataTable, Optional ByVal ShowDataType As Boolean = False) As String
        Dim strKeys As String = ""
        For Each objRow As DataRow In DBProperties.Rows
            If objRow.Item("fieldname") <> "" Then
                If Operation = "" OrElse objRow.Item("C") AndAlso Operation = "C" OrElse objRow.Item("R") AndAlso Operation = "R" OrElse objRow.Item("U") AndAlso Operation = "U" OrElse objRow.Item("D") AndAlso Operation = "D" OrElse objRow.Item("L") AndAlso Operation = "L" Then
                    If strKeys = "" Then
                        strKeys &= "@" & objRow.Item("fieldname")
                    Else
                        strKeys &= ",@" & objRow.Item("fieldname")
                    End If

                    If ShowDataType Then
                        strKeys &= " " & objRow.Item("sqldatatype") & GetFieldLength(objRow.Item("length")) & vbCrLf
                    Else
                        strKeys &= vbCrLf
                    End If
                End If
            End If
        Next

        Return strKeys
    End Function

    Private Function GetUpdateParmList(ByVal Operation As String, ByVal DBProperties As DataTable, Optional ByVal ShowDataType As Boolean = False) As String
        Dim strKeys As String = ""
        For Each objRow As DataRow In DBProperties.Rows
            If objRow.Item("fieldname") <> "" Then
                If Operation = "" OrElse objRow.Item("C") AndAlso Operation = "C" OrElse objRow.Item("R") AndAlso Operation = "R" OrElse objRow.Item("U") AndAlso Operation = "U" OrElse objRow.Item("D") AndAlso Operation = "D" OrElse objRow.Item("L") AndAlso Operation = "L" Then
                    ' filter out primary key fields '
                    If objRow.Item("PK") = False Then
                        If strKeys = "" Then
                            strKeys &= "SET " & objRow.Item("fieldname") & " = @" & objRow.Item("fieldname")
                        Else
                            strKeys &= "   ," & objRow.Item("fieldname") & " = @" & objRow.Item("fieldname")
                        End If

                        If ShowDataType Then
                            strKeys &= " " & objRow.Item("sqldatatype") & GetFieldLength(objRow.Item("length")) & vbCrLf
                        Else
                            strKeys &= vbCrLf
                        End If
                    End If

                End If
            End If
        Next

        Return strKeys
    End Function

    Private Function GetXMLLabelEntries(ByVal DBProperties As DataTable) As String
        Dim StringBuilder_Labels As New StringBuilder
        For Each objRow As DataRow In DBProperties.Rows
            If objRow.Item("fieldname") <> "" AndAlso objRow.Item("controltype") <> "" Then
                StringBuilder_Labels.AppendLine("<data name=""Label_" & objRow.Item("fieldname") & ".Text"" xml:space=""preserve"">")
                StringBuilder_Labels.AppendLine("<value>" & objRow.Item("fieldcaption") & "</value>")
                StringBuilder_Labels.AppendLine("<comment>Label text for field" & objRow.Item("fieldname") & "</comment>")
                StringBuilder_Labels.AppendLine("</data>")
            End If
        Next

        Return StringBuilder_Labels.ToString
    End Function

    Private Function GetXMLLabelEntries_Edit(ByVal DBProperties As DataTable) As String
        Dim StringBuilder_Labels As New StringBuilder
        For Each objRow As DataRow In DBProperties.Rows
            If objRow.Item("fieldname") <> "" AndAlso objRow.Item("controltype") <> "" Then
                StringBuilder_Labels.AppendLine("<data name=""Label_" & objRow.Item("fieldname") & ".Text"" xml:space=""preserve"">")
                StringBuilder_Labels.AppendLine("<value>" & objRow.Item("fieldcaption") & "</value>")
                StringBuilder_Labels.AppendLine("<comment>Label text for field" & objRow.Item("fieldname") & "</comment>")
                StringBuilder_Labels.AppendLine("</data>")
                StringBuilder_Labels.AppendLine("<data name=""Label_" & objRow.Item("fieldname") & ".Help"" xml:space=""preserve"">")
                If objRow.Item("helptext").ToString.Trim = "" Then
                    StringBuilder_Labels.AppendLine("<value>help text for field" & objRow.Item("fieldname") & "</value>")
                Else
                    StringBuilder_Labels.AppendLine("<value>" & System.Web.HttpUtility.HtmlEncode(objRow.Item("helptext")) & "</value>")
                End If

                StringBuilder_Labels.AppendLine("<comment>help text for field " & objRow.Item("fieldname") & "</comment>")
                StringBuilder_Labels.AppendLine("</data>")

                If objRow.Item("required") Then
                    StringBuilder_Labels.AppendLine("<data name=""RequiredFieldValidator_" & objRow.Item("fieldname") & ".ErrorMessage"" xml:space=""preserve"">")

                    If objRow.Item("validatortext").ToString.Trim = "" Then
                        StringBuilder_Labels.AppendLine("<value><br>" & objRow.Item("fieldname") & " is required.</value>")
                    Else
                        StringBuilder_Labels.AppendLine("<value>" & System.Web.HttpUtility.HtmlEncode((objRow.Item("validatortext"))) & "</value>")
                    End If


                    StringBuilder_Labels.AppendLine("<comment>required field validator err. message for field " & objRow.Item("fieldname") & "</comment>")
                    StringBuilder_Labels.AppendLine("</data>")
                End If
            End If
        Next

        Return StringBuilder_Labels.ToString
    End Function

    Private Function GetWhereClause(ByVal Operation As String, ByVal DBProperties As DataTable) As String
        Dim strFields As String = ""
        For Each objRow As DataRow In DBProperties.Rows
            If objRow.Item("fieldname") <> "" Then
                If Operation = "" OrElse objRow.Item("C") AndAlso Operation = "C" OrElse objRow.Item("R") AndAlso Operation = "R" OrElse objRow.Item("U") AndAlso Operation = "U" OrElse objRow.Item("D") AndAlso Operation = "D" OrElse objRow.Item("L") AndAlso Operation = "L" Then
                    If strFields = "" Then
                        strFields &= "WHERE " & objRow.Item("fieldname") & " = @" & objRow.Item("fieldname") & vbCrLf
                    Else
                        strFields &= "  AND " & objRow.Item("fieldname") & " = @" & objRow.Item("fieldname") & vbCrLf
                    End If
                End If
            End If
        Next

        Return strFields
    End Function

    Private Function GetVBDataType(ByVal typeName As String) As String
        Select Case typeName
            Case "numeric", "int"
                Return "Integer"
            Case "nvarchar", "varchar", "ntext", "text"
                Return "String"
            Case "money", "decimal"
                Return "Single"
            Case "smallint"
                Return "Int16"
            Case "datetime"
                Return "Date"
            Case "byte"
                Return "Byte"
            Case "bit"
                Return "Boolean"
            Case "char"
                Return "Char"
            Case "float"
                Return "Double"
            Case Else
                Return "[SQL DATATYPE UNDEFINED]"
        End Select
    End Function

End Class